package com.ccjq.shosestore.order.dao.impl;

import com.ccjq.shosestore.core.dbmanger.DbConnection;
import com.ccjq.shosestore.order.dao.OrderDao;
import com.ccjq.shosestore.order.po.CarAndProductPo;
import com.ccjq.shosestore.order.po.Order;
import com.ccjq.shosestore.order.po.OrderPo;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class OrderDaoImpl implements OrderDao {

    public int insertXinXi(OrderPo orderPo) {
        int result = 0;
        Connection con = null;
        PreparedStatement pre = null;
        ResultSet res = null;
        String sql = "insert into t_order values(?,?,?,?,?,?,?,?,?,?,?,?)";
        try {
            con = DbConnection.getConnection();
            pre = con.prepareStatement(sql);
            pre.setString(1,orderPo.getOrder_id());
            pre.setString(2,orderPo.getCreate_time());
            pre.setInt(3,orderPo.getPayment());
            pre.setDouble(4,orderPo.getAmonut());
            pre.setString(5,orderPo.getReceiver());
            pre.setString(6,orderPo.getProvince());
            pre.setString(7,orderPo.getCity());
            pre.setString(8,orderPo.getCounty());
            pre.setString(9,orderPo.getAddress());
            pre.setString(10,orderPo.getZipcode());
            pre.setString(11,orderPo.getTelphone());
            pre.setString(12,orderPo.getUser_id());
            result = pre.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbConnection.close(null, pre, con);
        }
        return result;
    }

    public int insertGuanLian(Order order) {
        int result = 0;
        Connection con = null;
        PreparedStatement pre = null;
        ResultSet res = null;
        String sql = "insert into t_order_product values(?,?,?,?)";
        try {
            con = DbConnection.getConnection();
            pre = con.prepareStatement(sql);
            pre.setString(1,order.getOrder_id());
            pre.setString(2,order.getProduct_id());
            pre.setInt(3,order.getQuantity());
            pre.setDouble(4,order.getPrice());
            result = pre.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbConnection.close(null, pre, con);
        }
        return result;
    }


    public int deleteCar(String user_id) {
        int result = 0;
        Connection con = null;
        PreparedStatement pre = null;
        ResultSet res = null;
        String sql = "delete from t_shopping_car where user_id=?";
        try {
            con = DbConnection.getConnection();
            pre = con.prepareStatement(sql);
            pre.setString(1,user_id);
            result = pre.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbConnection.close(null, pre, con);
        }
        return result;
    }

    public int updateStock(int quantity, String product_id) {
        int result = 0;
        Connection con = null;
        PreparedStatement pre = null;
        ResultSet res = null;
        String sql = "update t_stock set quantity=quantity-? where product_id=? ";
        try {
            con = DbConnection.getConnection();
            pre = con.prepareStatement(sql);
            pre.setInt(1,quantity);
            pre.setString(2,product_id);
            result = pre.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbConnection.close(null, pre, con);
        }
        return result;
    }

    public List<OrderPo> selectAllOrder() {
        List<OrderPo> list = new ArrayList<OrderPo>();
        Connection con = null;
        PreparedStatement pre = null;
        ResultSet res = null;
        String sql = "select * from t_order ";
        try {
            con = DbConnection.getConnection();
            pre = con.prepareStatement(sql);
            res = pre.executeQuery();
            while(res.next()){
                OrderPo orderPo = new OrderPo();
                orderPo.setOrder_id(res.getString(1));
                orderPo.setCreate_time(res.getString(2));
                orderPo.setPayment(res.getInt(3));
                orderPo.setAmonut(res.getDouble(4));
                orderPo.setReceiver(res.getString(5));
                orderPo.setProvince(res.getString(6));
                orderPo.setCity(res.getString(7));
                orderPo.setCounty(res.getString(8));
                orderPo.setAddress(res.getString(9));
                orderPo.setZipcode(res.getString(10));
                orderPo.setTelphone(res.getString(11));
                orderPo.setUser_id(res.getString(12));
                list.add(orderPo);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbConnection.close(res, pre, con);
        }
        return list;
    }

    public OrderPo selectOrder(String order_id) {
        OrderPo orderPo = new OrderPo();
        Connection con = null;
        PreparedStatement pre = null;
        ResultSet res = null;
        String sql = "select * from t_order where order_id=?";
        try {
            con = DbConnection.getConnection();
            pre = con.prepareStatement(sql);
            pre.setString(1,order_id);
            res = pre.executeQuery();
            if(res.next()){

                orderPo.setOrder_id(res.getString(1));
                orderPo.setCreate_time(res.getString(2));
                orderPo.setPayment(res.getInt(3));
                orderPo.setAmonut(res.getDouble(4));
                orderPo.setReceiver(res.getString(5));
                orderPo.setProvince(res.getString(6));
                orderPo.setCity(res.getString(7));
                orderPo.setCounty(res.getString(8));
                orderPo.setAddress(res.getString(9));
                orderPo.setZipcode(res.getString(10));
                orderPo.setTelphone(res.getString(11));
                orderPo.setUser_id(res.getString(12));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbConnection.close(res, pre, con);
        }
        return orderPo;
    }

    public List<CarAndProductPo> productCarList(String user_id) {
        List<CarAndProductPo> list = new ArrayList<CarAndProductPo>();
        Connection con = null;
        PreparedStatement pre = null;
        ResultSet res = null;
        String sql = "select shopping_cart_id,product_id,quantity,user_id from t_shopping_car where user_id=?";
        try {
            con = DbConnection.getConnection();
            pre = con.prepareStatement(sql);
            pre.setString(1,user_id);
            res = pre.executeQuery();
            while(res.next()){
                CarAndProductPo orderPo = new CarAndProductPo();
                orderPo.setShopping_cart_id(res.getString(1));
                orderPo.setProduct_id(res.getString(2));
                orderPo.setQuantity(res.getInt(3));
                orderPo.setUser_id(res.getString(4));
                list.add(orderPo);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbConnection.close(res, pre, con);
        }
        return list;
    }

    public List<CarAndProductPo> selectCarQuantity(String shopping_cart_id, String user_id) {
        List<CarAndProductPo> list = new ArrayList<CarAndProductPo>();
        Connection con = null;
        PreparedStatement pre = null;
        ResultSet res = null;
        String sql = "select product_id,quantity from t_shopping_car where shopping_cart_id=? and user_id=?";
        try {
            con = DbConnection.getConnection();
            pre = con.prepareStatement(sql);
            pre.setString(1,shopping_cart_id);
            pre.setString(2,user_id);
            res = pre.executeQuery();
            while(res.next()){
                CarAndProductPo orderPo = new CarAndProductPo();
                orderPo.setProduct_id(res.getString(2));
                orderPo.setQuantity(res.getInt(3));
                list.add(orderPo);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbConnection.close(res, pre, con);
        }
        return list;
    }

    public int selectStockQuantity(String product_id) {
        int result = 0;
        Connection con = null;
        PreparedStatement pre = null;
        ResultSet res = null;
        String sql = "select quantity from t_stock where product_id=?";
        try {
            con = DbConnection.getConnection();
            pre = con.prepareStatement(sql);
            pre.setString(1,product_id);
            res = pre.executeQuery();
            if(res.next()){
                result = res.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbConnection.close(res, pre, con);
        }
        return result;
    }

    public double selectPrice(String product_id) {
        double result = 0;
        Connection con = null;
        PreparedStatement pre = null;
        ResultSet res = null;
        String sql = "select price from t_product where product_id=?";
        try {
            con = DbConnection.getConnection();
            pre = con.prepareStatement(sql);
            pre.setString(1,product_id);
            res = pre.executeQuery();
            if(res.next()){
                result = res.getDouble(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbConnection.close(res, pre, con);
        }
        return result;
    }

    public List<OrderPo> selectFengYeAll(String user_id,int startRow, int pageSize) {
        System.out.println(user_id);
        List<OrderPo> list = new ArrayList<OrderPo>();
        Connection con = null;
        PreparedStatement pre = null;
        ResultSet res = null;
        String sql = " select * from\n" +
                "(select t.*, rownum rn from t_order t where t.user_id=?)t2\n" +
                "where t2.rn >=? and t2.rn <=?";
        try {
            con = DbConnection.getConnection();
            pre = con.prepareStatement(sql);
            pre.setString(1,user_id);
            pre.setInt(2,startRow);
            pre.setInt(3,pageSize);
            res = pre.executeQuery();
            while(res.next()) {
                OrderPo  orderPo = new OrderPo();
                orderPo.setOrder_id(res.getString(1));
                orderPo.setCreate_time(res.getString(2));
                orderPo.setPayment(res.getInt(3));
                orderPo.setAmonut(res.getDouble(4));
                orderPo.setReceiver(res.getString(5));
                orderPo.setProvince(res.getString(6));
                orderPo.setCity(res.getString(7));
                orderPo.setCounty(res.getString(8));
                orderPo.setAddress(res.getString(9));
                orderPo.setZipcode(res.getString(10));
                orderPo.setTelphone(res.getString(11));
                orderPo.setUser_id(res.getString(12));
                list.add(orderPo);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbConnection.close(res, pre, con);
        }
        return list;
    }

    public int pageCount(String user_id) {
        int count = 0;
        Connection con = null;
        PreparedStatement pre = null;
        ResultSet res = null;
        String sql = "select count(*) from t_order where user_id=?";
        try {
            con = DbConnection.getConnection();
            pre = con.prepareStatement(sql);
            pre.setString(1,user_id);
            res = pre.executeQuery();
            if(res.next()) {
                count=res.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbConnection.close(res, pre, con);
        }
        return count;
    }

    public List<CarAndProductPo> selectorder(String order_id) {
        List<CarAndProductPo> list = new ArrayList<CarAndProductPo>();
        Connection con = null;
        PreparedStatement pre = null;
        ResultSet res = null;
        String sql = "select b.product_id,b.feature,a.quantity,b.price from \n" +
                "t_order_product a full join t_product b on a.product_id=b.product_id where order_id=?";
        try {
            con = DbConnection.getConnection();
            pre = con.prepareStatement(sql);
            pre.setString(1,order_id);
            res = pre.executeQuery();
            while(res.next()){
                CarAndProductPo orderPo = new CarAndProductPo();
                orderPo.setProduct_id(res.getString(1));
                orderPo.setFeature(res.getString(2));
                orderPo.setQuantity(res.getInt(3));
                orderPo.setPrice(res.getDouble(4));
                list.add(orderPo);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbConnection.close(res, pre, con);
        }
        return list;
    }
}
